﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace 学院学生管理系统
{
    public partial class 学生学籍 : Form
    {
        public 学生学籍()
        {
            InitializeComponent();
        }

        private void 学生学籍_Load(object sender, EventArgs e)
        {
            no.Enabled = false;
            name.Enabled = false;
        }

        private void btnSelect_Click(object sender, EventArgs e)
        {
            string s;
            OleDbConnection a1 = new OleDbConnection();
            OleDbCommand a2 = new OleDbCommand();
            a1.ConnectionString = Class1.mas;
            a1.Open();
            if (cbTiaoJian.Text == "")
            {
                MessageBox.Show("请选择查询条件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else if (tbGJZ.Text == "")
            {
                MessageBox.Show("请输入关键字！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                if (cbTiaoJian.Text == "学号")
                {
                    s = "select * from 学生档案 where 学号='" + tbGJZ.Text.ToString().Trim() + "'";
                    a2.CommandText = s;
                    a2.Connection = a1;
                    OleDbDataReader a3 = a2.ExecuteReader();
                    if (a3.Read() == false)
                    {
                        MessageBox.Show("此学号尚未注册！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else
                    {
                        no.Text = a3[0].ToString();
                        name.Text = a3[1].ToString();
                        XiBie.Text = a3[2].ToString();
                        BanJi.Text = a3[3].ToString();
                        tbXingBie.Text = a3[4].ToString();
                        textBox1.Text = a3[5].ToString();
                        dtpBirth.Text = a3[6].ToString();
                        minzu.Text = a3[7].ToString();
                        tbMianMao.Text = a3[8].ToString();
                        zhuzhi.Text = a3[9].ToString();
                        postcodes.Text = a3[10].ToString();
                        phone.Text = a3[11].ToString();
                        dateTimePicker1.Text = a3[12].ToString();
                        pictureBox1.ImageLocation = a3[13].ToString();
                        beizhu.Text = a3[14].ToString();
                        MessageBox.Show("查询成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ace.oledb.12.0;Data Source=登录系统.accdb"); //Jet OLEDB:Database Password=
                        OleDbCommand cmd = conn.CreateCommand();

                        cmd.CommandText = "select * from 学生档案";
                        conn.Open();
                        OleDbDataReader dr = cmd.ExecuteReader();
                        DataTable dt = new DataTable();
                        if (dr.HasRows)
                        {
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                dt.Columns.Add(dr.GetName(i));
                            }
                            dt.Rows.Clear();
                        }
                        while (dr.Read())
                        {
                            DataRow row = dt.NewRow();
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                row[i] = dr[i];
                            }
                            dt.Rows.Add(row);
                        }
                        cmd.Dispose();
                        conn.Close();
                        dataGridView1.DataSource = dt;
                    }
                }
                if (cbTiaoJian.Text == "姓名")
                {
                    s = "select * from 学生档案 where 姓名='" + tbGJZ.Text.ToString().Trim() + "'";
                    a2.CommandText = s;
                    a2.Connection = a1;
                    OleDbDataReader a3 = a2.ExecuteReader();
                    if (a3.Read() == false)
                    {
                        MessageBox.Show("没有找到该同学！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else if (tbGJZ.Text == "")
                    {
                        MessageBox.Show("请输入关键字！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else
                    {
                        no.Text = a3[0].ToString();
                        name.Text = a3[1].ToString();
                        XiBie.Text = a3[2].ToString();
                        BanJi.Text = a3[3].ToString();
                        tbXingBie.Text = a3[4].ToString();
                        textBox1.Text = a3[5].ToString();
                        dtpBirth.Text = a3[6].ToString();
                        minzu.Text = a3[7].ToString();
                        tbMianMao.Text = a3[8].ToString();
                        zhuzhi.Text = a3[9].ToString();
                        postcodes.Text = a3[10].ToString();
                        phone.Text = a3[11].ToString();
                        dateTimePicker1.Text = a3[12].ToString();
                        pictureBox1.ImageLocation = a3[13].ToString();
                        beizhu.Text = a3[14].ToString();
                        MessageBox.Show("查询成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ace.oledb.12.0;Data Source=登录系统.accdb"); //Jet OLEDB:Database Password=
                        OleDbCommand cmd = conn.CreateCommand();

                        cmd.CommandText = "select * from 学生档案 where 姓名 ='" + name.Text.ToString() + "'";
                        conn.Open();
                        OleDbDataReader dr = cmd.ExecuteReader();
                        DataTable dt = new DataTable();
                        if (dr.HasRows)
                        {
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                dt.Columns.Add(dr.GetName(i));
                            }
                            dt.Rows.Clear();
                        }
                        while (dr.Read())
                        {
                            DataRow row = dt.NewRow();
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                row[i] = dr[i];
                            }
                            dt.Rows.Add(row);
                        }
                        cmd.Dispose();
                        conn.Close();
                        dataGridView1.DataSource = dt;
                    }
                }
                if (cbTiaoJian.Text == "班级")
                {
                    s = "select * from 学生档案 where 班级='" + tbGJZ.Text.ToString().Trim() + "'";
                    a2.CommandText = s;
                    a2.Connection = a1;
                    OleDbDataReader a3 = a2.ExecuteReader();
                    if (a3.Read() == false)
                    {
                        MessageBox.Show("没有找到此班级！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else if (tbGJZ.Text == "")
                    {
                        MessageBox.Show("请输入关键字！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else
                    {
                        no.Text = a3[0].ToString();
                        name.Text = a3[1].ToString();
                        XiBie.Text = a3[2].ToString();
                        BanJi.Text = a3[3].ToString();
                        tbXingBie.Text = a3[4].ToString();
                        textBox1.Text = a3[5].ToString();
                        dtpBirth.Text = a3[6].ToString();
                        minzu.Text = a3[7].ToString();
                        tbMianMao.Text = a3[8].ToString();
                        zhuzhi.Text = a3[9].ToString();
                        postcodes.Text = a3[10].ToString();
                        phone.Text = a3[11].ToString();
                        dateTimePicker1.Text = a3[12].ToString();
                        pictureBox1.ImageLocation = a3[13].ToString();
                        beizhu.Text = a3[14].ToString();
                        MessageBox.Show("查询成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ace.oledb.12.0;Data Source=登录系统.accdb"); //Jet OLEDB:Database Password=
                        OleDbCommand cmd = conn.CreateCommand();

                        cmd.CommandText = "select * from 学生档案 where 班级 ='" + BanJi.Text.ToString() + "'";
                        conn.Open();
                        OleDbDataReader dr = cmd.ExecuteReader();
                        DataTable dt = new DataTable();
                        if (dr.HasRows)
                        {
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                dt.Columns.Add(dr.GetName(i));
                            }
                            dt.Rows.Clear();
                        }
                        while (dr.Read())
                        {
                            DataRow row = dt.NewRow();
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                row[i] = dr[i];
                            }
                            dt.Rows.Add(row);
                        }
                        cmd.Dispose();
                        conn.Close();
                        dataGridView1.DataSource = dt;
                    }
                }
                if (cbTiaoJian.Text == "系别")
                {
                    s = "select * from 学生档案 where 系别='" + tbGJZ.Text.ToString().Trim() + "'";
                    a2.CommandText = s;
                    a2.Connection = a1;
                    OleDbDataReader a3 = a2.ExecuteReader();
                    if (a3.Read() == false)
                    {
                        MessageBox.Show("没有找到此系别！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else if (tbGJZ.Text == "")
                    {
                        MessageBox.Show("请输入关键字！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else
                    {
                        no.Text = a3[0].ToString();
                        name.Text = a3[1].ToString();
                        XiBie.Text = a3[2].ToString();
                        BanJi.Text = a3[3].ToString();
                        tbXingBie.Text = a3[4].ToString();
                        textBox1.Text = a3[5].ToString();
                        dtpBirth.Text = a3[6].ToString();
                        minzu.Text = a3[7].ToString();
                        tbMianMao.Text = a3[8].ToString();
                        zhuzhi.Text = a3[9].ToString();
                        postcodes.Text = a3[10].ToString();
                        phone.Text = a3[11].ToString();
                        dateTimePicker1.Text = a3[12].ToString();
                        pictureBox1.ImageLocation = a3[13].ToString();
                        beizhu.Text = a3[14].ToString();
                        MessageBox.Show("查询成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ace.oledb.12.0;Data Source=登录系统.accdb"); //Jet OLEDB:Database Password=
                        OleDbCommand cmd = conn.CreateCommand();

                        cmd.CommandText = "select * from 学生档案 where  系别='" + XiBie.Text.ToString() + "'";
                        conn.Open();
                        OleDbDataReader dr = cmd.ExecuteReader();
                        DataTable dt = new DataTable();
                        if (dr.HasRows)
                        {
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                dt.Columns.Add(dr.GetName(i));
                            }
                            dt.Rows.Clear();
                        }
                        while (dr.Read())
                        {
                            DataRow row = dt.NewRow();
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                row[i] = dr[i];
                            }
                            dt.Rows.Add(row);
                        }
                        cmd.Dispose();
                        conn.Close();
                        dataGridView1.DataSource = dt;
                    }
                }


            } 
        }

        private void btnXiuGai_Click(object sender, EventArgs e)
        {
            string t;
            t = "select * from 学生档案 where 学号='" + no.Text.Trim() + "'";
            OleDbConnection a1 = new OleDbConnection();
            OleDbCommand a2 = new OleDbCommand();
            a1.ConnectionString = Class1.mas;
            a1.Open();

            if (no.Text == "")
            {
                MessageBox.Show("请输入需要修改的学生的学号！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                t = "select * from 学生档案 where 学号='" + no.Text.Trim() + "'";
                a2.CommandText = t;
                a2.Connection = a1;
                OleDbDataReader a3 = a2.ExecuteReader();
                if (a3.Read() == false)
                {
                    MessageBox.Show("信息不存在！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    a3.Close();
                    string aw;
                    aw = "update 学生档案 set 姓名='" + name.Text.Trim() + "',系别='" + XiBie.Text.Trim() + "',班级='" + BanJi.Text.Trim() + "',性别='" + tbXingBie.Text.Trim() + "',身份证号='" + textBox1.Text.Trim() + "',出生日期='" + dtpBirth.Text.Trim() + "',民族='" + minzu.Text.Trim() + "',政治面貌='" + tbMianMao.Text.Trim() + "',家庭住址='" + zhuzhi.Text.Trim() + "',邮政编码='" + postcodes.Text.Trim() + "',联系电话='" + phone.Text.Trim() + "',入学日期='" + dateTimePicker1.Text.Trim() + "',照片='" + Program.Imagefile + "',备注='" + beizhu.Text.Trim() + "'where 学号='" + no.Text.Trim() + "'";
                    a2.CommandText = aw;
                    a2.Connection = a1;
                    a2.ExecuteNonQuery();
                    MessageBox.Show("修改信息成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ace.oledb.12.0;Data Source=登录系统.accdb"); //Jet OLEDB:Database Password=
                    OleDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "select * from 学生档案";
                    conn.Open();
                    OleDbDataReader dr = cmd.ExecuteReader();
                    DataTable dt = new DataTable();
                    if (dr.HasRows)
                    {
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            dt.Columns.Add(dr.GetName(i));
                        }
                        dt.Rows.Clear();
                    }
                    while (dr.Read())
                    {
                        DataRow row = dt.NewRow();
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            row[i] = dr[i];
                        }
                        dt.Rows.Add(row);
                    }
                    cmd.Dispose();
                    conn.Close();
                    dataGridView1.DataSource = dt;
                }

            }
        }
    }
}
